﻿using DB.Dapper;
using DB.EF;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using Orther.Model.param;
using Orther.Model.requestparam;
using Project.Utils;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VOL.Entity.DomainModels;
using static Dapper.SqlMapper;
using Microsoft.EntityFrameworkCore;
using EF.DB;
using VOL.Core.Extensions;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using MySqlConnector;
using VOL.Core.ManageUser;

namespace Orther.Service.Miyao
{
    public class WeixiudanService: BaseService<qx_wxd_nr>
    {

        private List<MySqlParameter> mysql_parameters { get; set; }
        private string mainSql = @"select a.*,c.kname as car_kname,c.dianhua as car_dianhua,c.carno as car_carno
                                ,c.chexing as car_chexing,c.chejiahao as car_chejiahao from qx_wxd_nr  a 
                                left join sys_user b on a.CreateID = b.User_Id 
                                left join qx_car c on a.carId = c.Id 
                                where 1=1 ";
        public List<qx_wxd_nr> GetPageList(WeixiudanParam param, Pagination pagination)
        {
            int total = 0;
            FilterClass filterClass = this.WhereFilter(param);
            List<qx_wxd_nr> list = DapperUtilsForMysql.PaginationZjc<qx_wxd_nr>(mainSql, pagination.PageIndex, pagination.PageSize, ref total, "CreateDate desc", filterClass.where, filterClass.parameters);
            list.ForEach(x =>
            {
                if (x.notselectdataCar != 1)
                {
                    x.car_carno= x.chepaihao;
                    x.car_chexing = x.chexing;
                    x.car_kname = x.kehuname;
                    x.car_chejiahao = x.chejiahao;
                    x.car_dianhua = x.dianhua;
                }
            });
            pagination.TotalCount = total;
            return list;
        }

        public async Task<List<qx_wxd_nr>> GetList(WeixiudanParam param, int? topNum)
        {
            FilterClass filterClass = this.WhereFilter(param);
            List<qx_wxd_nr> list = await DapperUtilsForMysql.queryList<qx_wxd_nr>(mainSql, filterClass.parameters, "CreateDate desc", filterClass.where, topNum);
            list.ForEach(x =>
            {
                if (x.notselectdataCar != 1)
                {
                    x.car_carno = x.chepaihao;
                    x.car_chexing = x.chexing;
                    x.car_kname = x.kehuname;
                    x.car_chejiahao = x.chejiahao;
                    x.car_dianhua = x.dianhua;
                }
            });
            return list;
        }

        public async Task<qx_wxd_nr> GetObject(WeixiudanParam param)
        {
            qx_wxd_nr entity = new qx_wxd_nr();
            List<qx_wxd_nr> list = await this.GetList(param, null);
            if (list.Count > 0)
            {
                entity = list[0];
                if (entity.notselectdataCar != 1)
                {
                    entity.car_carno = entity.chepaihao;
                    entity.car_chexing = entity.chexing;
                    entity.car_kname = entity.kehuname;
                    entity.car_chejiahao = entity.chejiahao;
                    entity.car_dianhua = entity.dianhua;
                }
            }
            return entity;
        }

        private FilterClass WhereFilter(WeixiudanParam param)
        {
            FilterClass filterClass = new FilterClass();
            mysql_parameters = new List<MySqlParameter>();


            if (param.Id != null)
            {
                filterClass.where = filterClass.where + " and a.Id=@Id ";
                filterClass.parameters.Add("@Id", param.Id);
                mysql_parameters.Add(new MySqlParameter("@Id", param.Id));
            }

            //默认不查询删除的
            if (param.SerchDel == null)
            {
                filterClass.where = filterClass.where + " and a.IsDeleted = 0 ";
            }

            if (param.deptIds != null && param.deptIds.Count > 0)
            {
                string insql = "";
                for (int i = 0; i < param.deptIds.Count; i++)
                {
                    if (i == 0)
                    {
                        insql = insql + " FIND_IN_SET('" + param.deptIds[0].ToString() + "', b.DeptIds) > 0 ";
                    }
                    else
                    {
                        insql = insql + " or FIND_IN_SET('" + param.deptIds[i].ToString() + "', b.DeptIds) > 0 ";
                    }

                }

                filterClass.where = filterClass.where + " and (" + insql + ") ";
            }

            if (param.riqi1 != null)
            {
                filterClass.where = filterClass.where + " and a.riqi >= @riqi1 ";
                filterClass.parameters.Add("@riqi1", param.riqi1);
                mysql_parameters.Add(new MySqlParameter("@riqi1", param.riqi1));
            }
            if (param.riqi2 != null)
            {
                filterClass.where = filterClass.where + " and a.riqi <= @riqi2 ";
                filterClass.parameters.Add("@riqi2", param.riqi2);
                mysql_parameters.Add(new MySqlParameter("@riqi2", param.riqi2));
            }
            if (param.riqi3 != null)
            {
                filterClass.where = filterClass.where + " and DATE(a.riqi) = @riqi3 ";
                filterClass.parameters.Add("@riqi3", param.riqi3);
                mysql_parameters.Add(new MySqlParameter("@riqi3", param.riqi3));
            }
            if (!string.IsNullOrEmpty(param.danhaoLike))
            {
                filterClass.where = filterClass.where + " and a.danhao like @danhaoLike ";
                filterClass.parameters.Add("@danhaoLike", '%' + param.danhaoLike + '%');
                mysql_parameters.Add(new MySqlParameter("@danhaoLike", '%' + param.danhaoLike + '%'));
            }
            if (!string.IsNullOrEmpty(param.chepaihaoLike))
            {
                filterClass.where = filterClass.where + " and c.carno like @chepaihaoLike ";
                filterClass.parameters.Add("@chepaihaoLike", '%' + param.chepaihaoLike + '%');
                mysql_parameters.Add(new MySqlParameter("@chepaihaoLike", '%' + param.chepaihaoLike + '%'));
            }
            if (!string.IsNullOrEmpty(param.chexingLike))
            {
                filterClass.where = filterClass.where + " and c.chexing like @chexingLike ";
                filterClass.parameters.Add("@chexingLike", '%' + param.chexingLike + '%');
                mysql_parameters.Add(new MySqlParameter("@chexingLike", '%' + param.chexingLike + '%'));
            }
            if (!string.IsNullOrEmpty(param.kehunameLike))
            {
                filterClass.where = filterClass.where + " and c.kname like @kehunameLike ";
                filterClass.parameters.Add("@kehunameLike", '%' + param.kehunameLike + '%');
                mysql_parameters.Add(new MySqlParameter("@kehunameLike", '%' + param.kehunameLike + '%'));
            }
            if (!string.IsNullOrEmpty(param.beizhuLike))
            {
                filterClass.where = filterClass.where + " and a.beizhu like @beizhuLike ";
                filterClass.parameters.Add("@beizhuLike", '%' + param.beizhuLike + '%');
                mysql_parameters.Add(new MySqlParameter("@beizhuLike", '%' + param.beizhuLike + '%'));
            }
            if (param.jiesuanfangshi != null)
            {
                filterClass.where = filterClass.where + " and a.jiesuanfangshi = @jiesuanfangshi ";
                filterClass.parameters.Add("@jiesuanfangshi", param.jiesuanfangshi);
                mysql_parameters.Add(new MySqlParameter("@jiesuanfangshi", param.jiesuanfangshi));
            }

            return filterClass;
        }


        public bool SaveForm(WeixiudanParams param, operatelog oLog)
        {
            qx_wxd_nr nrEntity = param.nr;
            List<qx_mx_peijian> list_peijian = param.list_peijian;
            List<qx_mx_weixiu> list_weixiu = param.list_weixiu;
            try
            {

                Action myAction = () =>
                {

                   
                    if (nrEntity.Id == Guid.Parse("00000000-0000-0000-0000-000000000000"))
                    {
                        nrEntity.Id = Guid.NewGuid();
                        nrEntity.IsDeleted = 0;
                        if (nrEntity.notselectdataCar == null)
                        {
                            nrEntity.notselectdataCar = 0;
                        }                        
                        //新增单据
                        mysqlContext.Set<qx_wxd_nr>().Add(nrEntity);
                        //批量添加配件明细
                        list_peijian.ForEach(x => {
                            x.nrId = nrEntity.Id;
                            x.CreateDate = nrEntity.CreateDate;
                            x.Creator = nrEntity.Creator;
                            x.CreateID = nrEntity.CreateID;
                            x.Id = Guid.NewGuid();
                        });
                        mysqlContext.Set<qx_mx_peijian>().AddRange(list_peijian);
                        //批量添加维修明细
                        list_weixiu.ForEach(x =>
                        {
                            x.Id = Guid.NewGuid();
                            x.nrId = nrEntity.Id;
                        });
                        mysqlContext.Set<qx_mx_weixiu>().AddRange(list_weixiu);
                        //记录日志
                        mysqlContext.Set<operatelog>().Add(oLog);
                    }
                    else
                    {
                        //更新单据
                        var entry = mysqlContext.Entry(nrEntity);
                        entry.State = EntityState.Modified;
                        //删除所有配件明细
                        List<qx_mx_peijian> delData = mysqlContext.qx_mx_peijians.Where(x => x.nrId == nrEntity.Id).ToList();
                       mysqlContext.Set<qx_mx_peijian>().RemoveRange(delData);
                        //重新批量添加配件明细
                        list_peijian.ForEach(x => {
                            x.nrId = nrEntity.Id;
                            x.Id = Guid.NewGuid();
                            x.CreateDate = x.CreateDate==null?DateTime.Now: x.CreateDate;
                            x.CreateID = x.CreateID == null ? UserContext.Current.UserId : x.CreateID;
                            x.Creator = x.Creator == null ? UserContext.Current.UserName : x.Creator;
                        });
                        mysqlContext.Set<qx_mx_peijian>().AddRange(list_peijian);
                        //删除所有维修明细
                        List<qx_mx_weixiu> delWxData = mysqlContext.qx_mx_weixius.Where(x => x.nrId == nrEntity.Id).ToList();
                        mysqlContext.Set<qx_mx_weixiu>().RemoveRange(delWxData);
                        //重新批量添加维修明细
                        list_weixiu.ForEach(x =>
                        {
                            x.Id = Guid.NewGuid();
                            x.nrId = nrEntity.Id;
                            x.CreateDate = x.CreateDate == null ? DateTime.Now : x.CreateDate;
                            x.CreateID = x.CreateID == null ? UserContext.Current.UserId : x.CreateID;
                            x.Creator = x.Creator == null ? UserContext.Current.UserName : x.Creator;
                        });
                        mysqlContext.Set<qx_mx_weixiu>().AddRange(list_weixiu);
                        //记录日志
                        mysqlContext.Set<operatelog>().Add(oLog);
                    }
                    //最后添加SaveChanges
                    mysqlContext.SaveChanges();
                };

                var transactionWrapper = new TransactionWrapper(mysqlContext);
                transactionWrapper.UseTransaction(myAction);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
                return false;
            }
        }

        public decimal[] GetSumJine(WeixiudanParam param)
        {
            FilterClass filterClass = this.WhereFilter(param);
            decimal[] numArray = new decimal[3] { 0, 0, 0 };
            decimal? num1 = mysqlContext.qx_wxd_nrs
                .FromSqlRaw(mainSql + filterClass.where, mysql_parameters.ToArray())
                .Sum(x=>x.peijiansum);
            numArray[0] = num1 == null ? 0 : num1.Value;

            decimal? num2 = mysqlContext.qx_wxd_nrs
                .FromSqlRaw(mainSql + filterClass.where, mysql_parameters.ToArray())
                .Sum(x => x.weixiusum);
            numArray[1] = num2 == null ? 0 : num2.Value;

            decimal? num3 = mysqlContext.qx_wxd_nrs
                .FromSqlRaw(mainSql + filterClass.where, mysql_parameters.ToArray())
                .Sum(x => x.zsum);
            numArray[2] = num3 == null ? 0 : num3.Value;

            return numArray;
        }
        public async Task<int> GetDayMax(WeixiudanParam param)
        {
            FilterClass filterClass = this.WhereFilter(param);
            //IgnoreQueryFilters 确保EF Core不应用查询过滤器
            int max = await mysqlContext.qx_wxd_nrs
                .FromSqlRaw(mainSql + filterClass.where, mysql_parameters.ToArray())
                .IgnoreQueryFilters().Where(x => x.riqi.Value.Date == DateTime.Now.Date).CountAsync();
            return max + 1;
        }

    }
}
